ExpirationRiskRecordV5

V8 Message Definiton

ExpirationRiskRecords contain account level position and risk summary detail. These records are published by AggRiskServers throughout the day approximately once per minute.

METADATA

Attribute	Value
Topic	4740-risk-v5
MLink Token	SystemData
Product	SRRisk
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
accnt	VARCHAR(16)	PRI	''
currency	enum - Currency	PRI	'None'
expiration	DATE	PRI	'1900-01-01'
clientFirm	VARCHAR(16)	PRI	''	SR assigned client firm
vegaLong	FLOAT		0	sum of long vega symbol positionssumorsvega if orsvega 0
vegaShort	FLOAT		0	sum of short vega symbol positions sumorsvega if orsvega 0
wVegaLong	FLOAT		0	sum of long weighted vega symbol positionssumorswVega if orswVega 0
wVegaShort	FLOAT		0	sum of short weighted vega symbol positions sumorswVega if orswVega 0
wtVegaLong	FLOAT		0	sum of long time weighted vega symbol positionssumorswtVega if orswtVega 0
wtVegaShort	FLOAT		0	sum of short time weighted vega symbol positions sumorswtVega if orswtVega 0
thetaLong	FLOAT		0	sum of long vega symbol positionssumorstheta if orstheta 0
thetaShort	FLOAT		0	sum of short vega symbol positions sumorstheta if orstheta 0
rhoLong	FLOAT		0	sum of long rho symbol positionssumorsrho if orsrho 0
rhoShort	FLOAT		0	sum of short rho symbol positions sumorsrho if orsrho 0
dGammaLong	FLOAT		0	sum of long gamma option positions
dGammaShort	FLOAT		0	sum of short gamma option positions
dBetaGaLong	FLOAT		0	sum of long betagamma option positions
dBetaGaShort	FLOAT		0	sum of short betagamma option positions
ivolLn	FLOAT		0	vega weighted average symbol ivol where orsvega 0
ivolSh	FLOAT		0	vega weighted average symbol ivol where orsvega 0
wtVeDd	FLOAT		0	Aggregate Dd Time Weighted VegasumwtVega if oprxde 030
wtVeDn	FLOAT		0	Aggregate Dn Time Weighted VegasumwtVega if 030 oprxde 010
wtVeAt	FLOAT		0	Aggregate At Time Weighted VegasumwtVega if absoprxde 010
wtVeUp	FLOAT		0	Aggregate Up Time Weighted VegasumwtVega if 010 oprxde 030
wtVeDu	FLOAT		0	Aggregate Du Time Weighted VegasumwtVega if 030 oprxde
absClrCn	INT		0	absolute number of contracts open clr
absCurCn	INT		0	absolute number of contracts open clr bot sld
premOvPar	FLOAT		0	aggregate option premium over parity
opPnlVol	FLOAT		0	options pnl using SR vol marks
opPnlMid	FLOAT		0	options pnl using SR mid marks
opPnlClr	FLOAT		0	options pnl using CLR mid marks
opDayVega	FLOAT		0	option vega traded today
opDayWVega	FLOAT		0	option vol weighted vega traded today
opDayTVega	FLOAT		0	option time weighted vega traded today
opDayWtVega	FLOAT		0	option vol time weighted vega traded today
opDayTheta	FLOAT		0	option theta traded today
opEdgeOpened	FLOAT		0	option theo edge opened today
opEdgeClosed	FLOAT		0	option theo edge closed today
pnlDn	FLOAT		0	option delta neutral pnlsumorspnlDn
pnlDe	FLOAT		0	option delta pnlsumorspnlDe
pnlSl	FLOAT		0	option vegadelta pnlsumorspnlSl
pnlGa	FLOAT		0	option gamma pnlsumorspnlGa
pnlTh	FLOAT		0	option theta pnlsumorspnlTh
pnlVe	FLOAT		0	option vega pnlsumorspnlVe
pnlVo	FLOAT		0	option volga pnlsumorspnlVo
pnlVa	FLOAT		0	option vanna pnlsumorspnlVa
pnlDDiv	FLOAT		0	option DDiv pnlsumorspnlDDiv
pnlSDiv	FLOAT		0	option SDiv pnlsumorspnlSDiv
pnlRate	FLOAT		0	option Rate pnlsumorspnlRate
pnlErr	FLOAT		0	option unexplained error pnlsumorspnlErr
pnlTe	FLOAT		0	option theo edge pnlsumorspnlTe
pnlLn	FLOAT		0	option pnl from option positions with vega
pnlSh	FLOAT		0	option pnl from option positions with vega
tEdge	FLOAT		0	aggregate option theo edgesumsrstEdge
tEdgeMult	FLOAT		0	denominator for computing edge per unitsumsrstEdgeMult
tEdgePr	FLOAT		0	aggregate option theo edge prior periodsumsrstEdgePr
tEdgeMultPr	FLOAT		0	enominator for computing edge per unit prior periodsumsrstEdgeMultPr
posTEdgePnl	FLOAT		0	aggregate pnl positive edge symbolssumorsposTEdgePnl
negTEdgePnl	FLOAT		0	aggregate pnl negative edge symbolssumorsnegTEdgePnl
badTEdgePnl	FLOAT		0	aggregate pnl no theo edge symbolssumorsbadTEdgePnl
VaRsu90	FLOAT		0	Aggregate RiskSlide uPrc up 90 vol unchanged newUPrc uPrc MathExp090
VaRsd90	FLOAT		0	Aggregate RiskSlide uPrc dn 90 vol unchanged newUPrc uPrc MathExp090
VaRsu50	FLOAT		0	Aggregate RiskSlide uPrc up 50 vol unchanged newUPrc uPrc MathExp050
VaRsd50	FLOAT		0	Aggregate RiskSlide uPrc dn 50 vol unchanged newUPrc uPrc MathExp050
VaRsu15	FLOAT		0	Aggregate RiskSlide uPrc up 15 vol unchanged newUPrc uPrc MathExp015
VaRsd15	FLOAT		0	Aggregate RiskSlide uPrc dn 15 vol unchanged newUPrc uPrc MathExp015
VaRsu10	FLOAT		0	Aggregate RiskSlide uPrc up 10 vol unchanged newUPrc uPrc MathExp010
VaRsd10	FLOAT		0	Aggregate RiskSlide uPrc dn 10 vol unchanged newUPrc uPrc MathExp010
VaRsu05	FLOAT		0	Aggregate RiskSlide uPrc up 5 vol unchanged newUPrc uPrc MathExp005
VaRsd05	FLOAT		0	Aggregate RiskSlide uPrc dn 5 vol unchanged newUPrc uPrc MathExp005
VaRsu1e	FLOAT		0	Aggregate RiskSlide uPrc up 1x implied earn move vol ramp out
VaRsd1e	FLOAT		0	Aggregate RiskSlide uPrc dn 1x implied earn move vol ramp out
VaRsu2e	FLOAT		0	Aggregate RiskSlide uPrc up 2x implied earn move vol ramp out
VaRsd2e	FLOAT		0	Aggregate RiskSlide uPrc dn 2x implied earn move vol ramp out
VaRearn	FLOAT		0	Aggregate RiskSlide vol earn ramp out no uPrc move
VaRcash	FLOAT		0	Aggregate RiskSlide uPrc up 30 vol 001 6mn deal close delta neutral
lastActivity	DATETIME(6)		'1900-01-01 00:00:00.000000'
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
accnt	1
currency	2
expiration	3
clientFirm	4

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRRisk`.`MsgExpirationRiskRecordV5` (
    `accnt` VARCHAR(16) NOT NULL DEFAULT '',
    `currency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `expiration` DATE NOT NULL DEFAULT '1900-01-01',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned client firm',
    `vegaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long vega symbol positions;=sum(+ors.vega) if ors.vega > 0',
    `vegaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short vega symbol positions;= sum(-ors.vega) if ors.vega < 0',
    `wVegaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long weighted vega symbol positions;=sum(+ors.wVega) if ors.wVega > 0',
    `wVegaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short weighted vega symbol positions;= sum(-ors.wVega) if ors.wVega < 0',
    `wtVegaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long time weighted vega symbol positions;=sum(+ors.wtVega) if ors.wtVega > 0',
    `wtVegaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short time weighted vega symbol positions;= sum(-ors.wtVega) if ors.wtVega < 0',
    `thetaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long vega symbol positions;=sum(+ors.theta) if ors.theta > 0',
    `thetaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short vega symbol positions;= sum(-ors.theta) if ors.theta < 0',
    `rhoLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long rho symbol positions;=sum(+ors.rho) if ors.rho > 0',
    `rhoShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short rho symbol positions;= sum(-ors.rho) if ors.rho < 0',
    `dGammaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long $ gamma option positions',
    `dGammaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short $gamma option positions',
    `dBetaGaLong` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of long $ beta/gamma option positions',
    `dBetaGaShort` FLOAT NOT NULL DEFAULT 0 COMMENT 'sum of short $ beta/gamma option positions',
    `ivolLn` FLOAT NOT NULL DEFAULT 0 COMMENT 'vega weighted average symbol ivol where ors.vega > 0',
    `ivolSh` FLOAT NOT NULL DEFAULT 0 COMMENT 'vega weighted average symbol ivol where ors.vega < 0',
    `wtVeDd` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate Dd Time Weighted Vega;=sum[wtVega] if opr.xde < -0.30',
    `wtVeDn` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate Dn Time Weighted Vega;=sum[wtVega] if -0.30 <= opr.xde < -0.10',
    `wtVeAt` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate At Time Weighted Vega;=sum[wtVega] if abs(opr.xde) <= 0.10',
    `wtVeUp` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate Up Time Weighted Vega;=sum[wtVega] if +0.10 < opr.xde <= +0.30',
    `wtVeDu` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate Du Time Weighted Vega;=sum[wtVega] if +0.30 < opr.xde',
    `absClrCn` INT NOT NULL DEFAULT 0 COMMENT 'absolute number of contracts (open clr)',
    `absCurCn` INT NOT NULL DEFAULT 0 COMMENT 'absolute number of contracts (open clr + bot - sld)',
    `premOvPar` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate option premium over parity',
    `opPnlVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'options pnl (using SR vol marks)',
    `opPnlMid` FLOAT NOT NULL DEFAULT 0 COMMENT 'options pnl (using SR mid marks)',
    `opPnlClr` FLOAT NOT NULL DEFAULT 0 COMMENT 'options pnl (using CLR mid marks)',
    `opDayVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vega traded today',
    `opDayWVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'option (vol weighted) vega traded today',
    `opDayTVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'option (time weighted) vega traded today',
    `opDayWtVega` FLOAT NOT NULL DEFAULT 0 COMMENT 'option (vol time weighted) vega traded today',
    `opDayTheta` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theta traded today',
    `opEdgeOpened` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theo edge opened today',
    `opEdgeClosed` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theo edge closed today',
    `pnlDn` FLOAT NOT NULL DEFAULT 0 COMMENT 'option delta neutral pnl;=sum(ors.pnlDn)',
    `pnlDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'option delta pnl;=sum(ors.pnlDe)',
    `pnlSl` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vega/delta pnl;=sum(ors.pnlSl)',
    `pnlGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'option gamma pnl;=sum(ors.pnlGa)',
    `pnlTh` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theta pnl;=sum(ors.pnlTh)',
    `pnlVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vega pnl;=sum(ors.pnlVe)',
    `pnlVo` FLOAT NOT NULL DEFAULT 0 COMMENT 'option volga pnl;=sum(ors.pnlVo)',
    `pnlVa` FLOAT NOT NULL DEFAULT 0 COMMENT 'option vanna pnl;=sum(ors.pnlVa)',
    `pnlDDiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'option DDiv pnl;=sum(ors.pnlDDiv)',
    `pnlSDiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'option SDiv pnl;=sum(ors.pnlSDiv)',
    `pnlRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'option Rate pnl;=sum(ors.pnlRate)',
    `pnlErr` FLOAT NOT NULL DEFAULT 0 COMMENT 'option unexplained (error) pnl;=sum(ors.pnlErr)',
    `pnlTe` FLOAT NOT NULL DEFAULT 0 COMMENT 'option theo edge pnl;=sum(ors.pnlTe)',
    `pnlLn` FLOAT NOT NULL DEFAULT 0 COMMENT 'option pnl from option positions with +vega',
    `pnlSh` FLOAT NOT NULL DEFAULT 0 COMMENT 'option pnl from option positions with -vega',
    `tEdge` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate option theo edge;=sum(srs.tEdge)',
    `tEdgeMult` FLOAT NOT NULL DEFAULT 0 COMMENT 'denominator for computing edge per unit;=sum(srs.tEdgeMult)',
    `tEdgePr` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate option theo edge (prior period);=sum(srs.tEdgePr)',
    `tEdgeMultPr` FLOAT NOT NULL DEFAULT 0 COMMENT 'enominator for computing edge per unit (prior period);=sum(srs.tEdgeMultPr)',
    `posTEdgePnl` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate pnl (positive edge symbols);=sum(ors.posTEdgePnl)',
    `negTEdgePnl` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate pnl (negative edge symbols);=sum(ors.negTEdgePnl)',
    `badTEdgePnl` FLOAT NOT NULL DEFAULT 0 COMMENT 'aggregate pnl (no theo edge symbols);=sum(ors.badTEdgePnl)',
    `VaRsu90` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 90%, vol unchanged (newUPrc = uPrc * Math.Exp(+0.90))',
    `VaRsd90` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 90%, vol unchanged (newUPrc = uPrc * Math.Exp(-0.90))',
    `VaRsu50` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 50%, vol unchanged (newUPrc = uPrc * Math.Exp(+0.50))',
    `VaRsd50` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 50%, vol unchanged (newUPrc = uPrc * Math.Exp(-0.50))',
    `VaRsu15` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 15%, vol unchanged (newUPrc = uPrc * Math.Exp(+0.15))',
    `VaRsd15` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 15%, vol unchanged (newUPrc = uPrc * Math.Exp(-0.15))',
    `VaRsu10` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 10%, vol unchanged (newUPrc = uPrc * Math.Exp(+0.10))',
    `VaRsd10` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 10%, vol unchanged (newUPrc = uPrc * Math.Exp(-0.10))',
    `VaRsu05` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 5%, vol unchanged (newUPrc = uPrc * Math.Exp(+0.05))',
    `VaRsd05` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 5%, vol unchanged (newUPrc = uPrc * Math.Exp(-0.05))',
    `VaRsu1e` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 1x implied earn move, vol ramp out',
    `VaRsd1e` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 1x implied earn move, vol ramp out',
    `VaRsu2e` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 2x implied earn move, vol ramp out',
    `VaRsd2e` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc dn 2x implied earn move, vol ramp out',
    `VaRearn` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: vol earn ramp out (no uPrc move)',
    `VaRcash` FLOAT NOT NULL DEFAULT 0 COMMENT 'Aggregate RiskSlide: uPrc up 30%, vol = 0.01, 6mn deal close (delta neutral)',
    `lastActivity` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`accnt`,`currency`,`expiration`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='ExpirationRiskRecords contain account level position and risk summary detail. These records are published by AggRiskServers throughout the day approximately once per minute.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `accnt`,
    `currency`,
    `expiration`,
    `clientFirm`,
    `vegaLong`,
    `vegaShort`,
    `wVegaLong`,
    `wVegaShort`,
    `wtVegaLong`,
    `wtVegaShort`,
    `thetaLong`,
    `thetaShort`,
    `rhoLong`,
    `rhoShort`,
    `dGammaLong`,
    `dGammaShort`,
    `dBetaGaLong`,
    `dBetaGaShort`,
    `ivolLn`,
    `ivolSh`,
    `wtVeDd`,
    `wtVeDn`,
    `wtVeAt`,
    `wtVeUp`,
    `wtVeDu`,
    `absClrCn`,
    `absCurCn`,
    `premOvPar`,
    `opPnlVol`,
    `opPnlMid`,
    `opPnlClr`,
    `opDayVega`,
    `opDayWVega`,
    `opDayTVega`,
    `opDayWtVega`,
    `opDayTheta`,
    `opEdgeOpened`,
    `opEdgeClosed`,
    `pnlDn`,
    `pnlDe`,
    `pnlSl`,
    `pnlGa`,
    `pnlTh`,
    `pnlVe`,
    `pnlVo`,
    `pnlVa`,
    `pnlDDiv`,
    `pnlSDiv`,
    `pnlRate`,
    `pnlErr`,
    `pnlTe`,
    `pnlLn`,
    `pnlSh`,
    `tEdge`,
    `tEdgeMult`,
    `tEdgePr`,
    `tEdgeMultPr`,
    `posTEdgePnl`,
    `negTEdgePnl`,
    `badTEdgePnl`,
    `VaRsu90`,
    `VaRsd90`,
    `VaRsu50`,
    `VaRsd50`,
    `VaRsu15`,
    `VaRsd15`,
    `VaRsu10`,
    `VaRsd10`,
    `VaRsu05`,
    `VaRsd05`,
    `VaRsu1e`,
    `VaRsd1e`,
    `VaRsu2e`,
    `VaRsd2e`,
    `VaRearn`,
    `VaRcash`,
    `lastActivity`,
    `timestamp`
FROM `SRRisk`.`MsgExpirationRiskRecordV5`
WHERE 
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt'
  AND
    /* Replace with a ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') */ 
    `currency` = 'None'
  AND
    /* Replace with a DATE */
    `expiration` = '2022-01-01'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRRisk.doccolumns WHERE TABLE_NAME='ExpirationRiskRecordV5' ORDER BY ordinal_position ASC;